<?php

class db {

    private $dbhandler = NULL;
    private $version = NULL;
    private $dbname = "sorter.db";
    private $use_db;
    public $sql = NULL;
    public $lastid = NULL;
    private $config;

    function __construct($config) {
        $this->config = $config;
        if ($config["DB"]["db_file"])
            $this->dbname = $config["DB"]["db_file"];
        $this->use_db = $config["DB"]["use_db"];
        if (!class_exists("SQLite3"))
            die("SQLite3 not found, check php settings!\n");
        if (!file_exists($this->dbname)) {
            //print "DB not exist, creating\n";
            $newdb = 1;
        }
        else
            $newdb = 0;
        try {
            $this->dbhandler = new SQLite3($this->dbname);
        } catch (Exception $e) {
            die("Cannot open DB {$this->dbname}, check permission of root web folder\n");
        }
        if (!$this->dbhandler)
            die("There's something wrong with db instance, file {$this->dbname}!\n");
        $this->version = SQLite3::version();
        $this->version = $this->version['versionString'];
        if ($newdb)
            $this->create_tables();
        chmod($this->dbname, 0777);
    }

    // HIGH LEVEL FUNCTIONS

    public function create_tables() {
        // db instanced?
        if (!$this->dbhandler)
            die("DB not instanced\n");
        // table creation
        $this->dbhandler->exec('CREATE TABLE downloaded (id INTEGER PRIMARY KEY ASC, download_date TEXT, name TEXT, tvshow_season NUMERIC, tvshow_episode NUMERIC, size TEXT, quality TEXT, imdb_year TEXT, imdb_country TEXT, imdb_actors TEXT, imdb_synopsis TEXT, imdb_release_date TEXT, imdb_link TEXT, imdb_rating NUMERIC, imdb_picture NUMERIC, imdb_genres TEXT)');
        $this->dbhandler->exec('CREATE TABLE torrent (id INTEGER PRIMARY KEY ASC, torrent_date TEXT, link TEXT, size TEXT, seeds NUMERIC, peers NUMERIC, name TEXT, tvshow_season NUMERIC, tvshow_episode NUMERIC, quality TEXT, imdb_year TEXT, imdb_country TEXT, imdb_actors TEXT, imdb_synopsis TEXT, imdb_release_date TEXT, imdb_link TEXT, imdb_rating NUMERIC, imdb_picture NUMERIC, imdb_genres TEXT)');
        $this->dbhandler->exec('CREATE TABLE settings (id INTEGER PRIMARY KEY ASC, section TEXT, name TEXT, value TEXT)');
        $this->dbhandler->exec('CREATE TABLE version (id INTEGER PRIMARY KEY ASC, name TEXT, major NUMERIC, minor NUMERIC)');
        $this->dbhandler->exec('CREATE TABLE user (id INTEGER PRIMARY KEY ASC, name TEXT, password TEXT, access NUMERIC)');
        $this->dbhandler->exec('CREATE TABLE log (id INTEGER PRIMARY KEY ASC, logdate TEXT, section TEXT, message TEXT)');
        // insert autosort version
        $this->dbhandler->exec('INSERT INTO version (name,major,minor) VALUES ("autosorter",0,3)');
        $this->dbhandler->exec('INSERT INTO version (name,major,minor) VALUES ("db",1,0)');
        $this->dbhandler->exec('INSERT INTO user (name,password,access) VALUES ("admin","admin",0)');
    }

    public function escape($data) {
        if (is_object($data)) {
            print "Fatal ERROR: Escaping data is of type ".gettype($data);
            var_dump($data);
            die;
        }
        $search = array('&', '"', "'", '<', '>');
        $replace = array('&amp;', '&quot;', '&#39;', '&lt;', '&gt;');
        $data = str_replace($search, $replace, $data);
        return $data;
    }

    public function addtolog($section, $message) {
        $now = time();
        $this->sql = "INSERT INTO log (logdate,section,message) VALUES ({$now},'{$section}','{$message}')";
        $r = $this->dbhandler->exec($this->sql);
        return $r;
    }

    public function add_new_content($name, $tvseason = "", $tveps = "", $filesize, $imdb, $quality = "N/A") {
        if (empty($tvseason))
            $row = array(
                "download_date" => time(),
                "name" => strtolower($name),
                "tvshow_season" => $tvseason,
                "tvshow_episode" => $tveps,
                "size" => $filesize,
                "quality" => $quality,
                "imdb_year" => $imdb[0]->year,
                "imdb_country" => implode(",", $imdb[0]->country),
                "imdb_actors" => implode(",", $imdb[0]->actors),
                "imdb_synopsis" => $imdb[0]->plot_simple,
                "imdb_release_date" => $imdb[0]->release_date,
                "imdb_link" => $imdb[0]->imdb_url,
                "imdb_rating" => $imdb[0]->rating,
                "imdb_picture" => $imdb[0]->poster->cover,
                "imdb_genres" => implode(",", $imdb[0]->genres),
            );
        else
            $row = array(
                "download_date" => time(),
                "name" => strtolower($name),
                "tvshow_season" => $tvseason,
                "tvshow_episode" => $tveps,
                "size" => $filesize,
                "quality" => $quality,
            );
        if (!DEBUG_SIMULATE)
            $this->insert_rows("downloaded", array($row));
    }

    public function torrent_add_new_content($name, $tvseason = "", $tveps = "", $filesize, $imdb, $quality = "N/A", $link = "", $seeds = "", $peers = "") {
        if (empty($tvseason))
            $row = array(
                "torrent_date" => time(),
                "link" => $link,
                "size" => $filesize,
                "seeds" => $seeds,
                "peers" => $peers,
                "name" => strtolower($name),
                "tvshow_season" => $tvseason,
                "tvshow_episode" => $tveps,
                "quality" => $quality,
                "imdb_year" => $imdb[0]->year,
                "imdb_country" => implode(",", $imdb[0]->country),
                "imdb_actors" => implode(",", $imdb[0]->actors),
                "imdb_synopsis" => $imdb[0]->plot_simple,
                "imdb_release_date" => $imdb[0]->release_date,
                "imdb_link" => $imdb[0]->imdb_url,
                "imdb_rating" => $imdb[0]->rating,
                "imdb_picture" => $imdb[0]->poster->cover,
                "imdb_genres" => implode(",", $imdb[0]->genres),
            );
        else
            $row = array(
                "torrent_date" => time(),
                "name" => strtolower($name),
                "tvshow_season" => $tvseason,
                "tvshow_episode" => $tveps,
                "size" => $filesize,
                "quality" => $quality,
            );
        if (!DEBUG_SIMULATE)
            $this->insert_rows("torrent", array($row));
    }

    /**
     * Check if downloaded content exists
     *
     * @param type $title title to check
     * @param type $season season to check
     * @param type $episode episode to check
     * @return boolean true if exists, false otherwise
     */
    public function downloaded_content_exist($title, $season = NULL, $episode = NULL, $table1c = NULL, $table2c = NULL) {
        $table1 = "downloaded";
        $table2 = "torrent";
        $field = "name";
        $value = strtolower($title);
        $value1 = trim(str_replace("the", "", $value));
        $value1 = trim(str_replace(",the", "", $value1));
        $value2 = "the " . $value1;
        //$r = $r1 = $r2 = $r3 = $r4 = $r5 = false;
        if ($season && $episode) {  // tv episode detected
            $r = $this->get_rows($table1, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
            $r1 = $this->get_rows($table2, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
            $r2 = $this->get_rows($table1, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
            $r3 = $this->get_rows($table2, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
            $r4 = $this->get_rows($table1, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
            $r5 = $this->get_rows($table2, "name='{$value}' AND tvshow_season='{$season}' AND tvshow_episode='{$episode}'");
        } else {    // movie detected
            $r = $this->check_existing_row($table1, $field, $value);
            $r1 = $this->check_existing_row($table2, $field, $value);
            $r2 = $this->check_existing_row($table1, $field, $value1);
            $r3 = $this->check_existing_row($table2, $field, $value1);
            $r4 = $this->check_existing_row($table1, $field, $value2);
            $r5 = $this->check_existing_row($table2, $field, $value2);
        }
        if ($table1c && $table2c) {
            if ($r || $r1 || $r2 || $r3 || $r4 || $r5)
                $res = true;
            else
                $res = false;
        } else
        if ($table1c) {
            if ($r || $r2 || $r4)
                $res = true;
            else
                $res = false;
        } else
        if ($table2c) {
            if ($r1 || $r3 || $r5)
                $res = true;
            else
                $res = false;
        }
        return $res;
    }

    public function check_existing_row($table, $field, $value) {
        $condition = "{$field}='{$value}'";
        $row = $this->get_row($table, $condition);
        if (!$row || empty($row))
            return false;
        return true;
    }

    // LOW LEVEL FUNCTIONS

    public function change_rows($table, $set, $where) {
        $setfields = "";
        foreach ($set as $setvar => $setval) {
            if (is_object($setval)) {
                    print "Passed value as object in change_rows!\n";
                    var_dump($value);die;
                }
            $setval_escaped = $this->escape($setval);
            $setfields.="{$setvar}='$setval_escaped'";
        }
        $this->sql = "UPDATE {$table} SET ({$fields}) VALUES ({$values})";
        $this->dbhandler->exec($this->sql);
    }

    public function delete_rows($table, $condition = NULL) {
        if ($condition)
            $this->sql = "DELETE FROM {$table} WHERE {$condition}";
        else
            $this->sql = "DELETE FROM {$table}";
        $this->dbhandler->exec($this->sql);
    }

    public function insert_rows($table, array $rows, $extra = "") {
        foreach ($rows as $row) {
            $fileds = $values = "";
            foreach ($row as $field => $value) {
                if (is_object($value)) {
                    print "Passed value as object in insert_rows!\n";
                    var_dump($value);die;
                }
                $value_escaped = $this->escape($value);
                $fields.="'" . $field . "',";
                $values.="'" . $value_escaped . "',";
            }
            $fields = substr($fields, 0, strlen($fields) - 1);
            $values = substr($values, 0, strlen($values) - 1);
            $this->sql = "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
            $this->dbhandler->exec($this->sql);
        }
        $this->lastid = $this->db->lastInsertRowID;
    }

    public function get_rows($table, $condition = false, $other = "") {
        if ($condition)
            $this->sql = "SELECT * FROM {$table} WHERE {$condition} {$other}";
        else
            $this->sql = "SELECT * FROM {$table} {$other}";
        $results = $this->dbhandler->query($this->sql);
        if ($results === false)
            return false;
        $cols = $results->numColumns();
        while ($row = $results->fetchArray()) {
            for ($i = 0; $i < $cols; $i++)
                $rows[$results->columnName($i)] = $row[$i];
            $finalrow[] = $rows;
        }
        return $finalrow;
    }

    public function get_row($table, $condition = false) {
        if ($condition)
            $this->sql = "SELECT * FROM {$table} WHERE {$condition} LIMIT 1";
        else
            $this->sql = "SELECT * FROM {$table} LIMIT 1";
        $results = $this->dbhandler->query($this->sql);
        if (!$results)    // no result or error
            return false;
        $cols = $results->numColumns();
        $row = $results->fetchArray();
        if ($row === false)   // no result
            return "";
        for ($i = 1; $i < $cols; $i++)
            $rows[$results->columnName($i)] = $row[$i];
        $finalrow[] = $rows;
        return $finalrow;
    }

    public function sql($query) {
        $this->sql = $query;
        $results = $this->dbhandler->query($this->sql);
        $cols = $results->numColumns();
        while ($row = $results->fetchArray()) {
            for ($i = 0; $i < $cols; $i++)
                $rows[$results->columnName($i)] = $row[$i];
            $finalrow[] = $rows;
        }
        return $finalrow;
    }

    public function close() {
        $this->dbhandler->close();
    }

}

?>
